
/*remise dans le bon sens de masque-var*/
-- masque_var_trans;


define erp='erp_mli2.';
define outils='outils_mli2.';


 /*
select * from (
SELECT &outils.stragg(source) source,champsais_cod, champctav_typ, enreg_id, grille_cod, bornemsg_lib, complsqlgrille_lib, complsqltable_lib, entitecle_cod, entiteent_cod, entiteslig_cod, grillechamp_cod, enreg_s, formatcol_val, msg_lib, borne_typ, borneinf_val, bornesup_val, valeur_typ, msgvaleur_lib
FROM (
  SELECT 'uv' source ,g.* FROM se_GEN_CHAMPCTAV g where exists (select 1 from se_GEN_CHAMPCTAV gg where gg.champsais_cod=g.champsais_cod)

  UNION ALL
  SELECT 'se'
      --  || v.vrt_cod || v.att_cod|| (select &outils.stragg(valeur_val) from &erp.gen_champctavval vv where vv.champsais_cod=g.champsais_cod)
      source ,g.* FROM &erp.GEN_CHAMPCTAV g,&erp.ART_GENECHAPATT v where v.champsais_cod=g.champsais_cod
  )

GROUP BY champsais_cod, champctav_typ, enreg_id, grille_cod, bornemsg_lib, complsqlgrille_lib, complsqltable_lib, entitecle_cod, entiteent_cod, entiteslig_cod, grillechamp_cod, enreg_s, formatcol_val, msg_lib, borne_typ, borneinf_val, bornesup_val, valeur_typ, msgvaleur_lib
HAVING Count(1)!=2 ) r
ORDER BY CHAMPCTAV_TYP,champsais_cod,source  */

 --CREATE TABLE se_GEN_CHAMPCTAVval AS (SELECT * FROM  &erp.GEN_CHAMPCTAVval WHERE 1=2);

 delete from se_GEN_CHAMPCTAVval;

 INSERT INTO se_GEN_CHAMPCTAVval
 (SELECT m.champsais_cod,
         'C' champctav_typ,
         /*enleve les points inutiles devant et derriere*/
         REGEXP_REPLACE(REPLACE(l.lon,'....','STD'),'^([\.]{0,3})(.+?)([\.]*)$','\2') valeur_val,
         0 enreg_id,
         'N' enreg_s,
         l.RANGMULTI2+1 champctavval_rang
     FROM  se_ART_GENECHAPATT m , se_VRT_ATT v,se_GEN_CHAMPCTAV_vrtatt g,uv_cmodeles_lon l
     WHERE m.vrt_cod=v.vrt_cod AND m.vrtattatt_cod=v.att_cod  AND g.champsais_cod=v.champsais_cod
       AND g.CHAMPCTAV_TYP='C' AND  m.vrt_cod = 'LON' AND m.vrtattatt_cod='1'
       AND l.id=m.gene_cod || m.gene_ind
  );

  INSERT INTO se_GEN_CHAMPCTAVval
 (SELECT m.champsais_cod,
         'C' champctav_typ,
         /*enleve les points inutiles devant et derriere*/
         REGEXP_REPLACE(REPLACE(l.tai,'....','STD'),'^([\.]{0,3})(.+?)([\.]*)$','\2') valeur_val,
         0 enreg_id,
         'N' enreg_s,
         l.RANGMULTI2+1 champctavval_rang
     FROM  se_ART_GENECHAPATT m , se_VRT_ATT v,se_GEN_CHAMPCTAV_vrtatt g,uv_cmodeles_tai l
     WHERE m.vrt_cod=v.vrt_cod AND m.vrtattatt_cod=v.att_cod  AND g.champsais_cod=v.champsais_cod
       AND g.CHAMPCTAV_TYP='C' AND  m.vrt_cod = 'TAI' AND m.vrtattatt_cod='1'
       AND l.id=m.gene_cod || m.gene_ind
  );


 UPDATE se_GEN_CHAMPCTAVval tf SET enreg_id=Nvl((select enreg_id FROM &erp.GEN_CHAMPCTAVval f WHERE f.champsais_cod=tf.champsais_cod and f.CHAMPCTAV_TYP=tf.CHAMPCTAV_TYP AND f.valeur_val=tf.valeur_val),0) ;
 UPDATE se_GEN_CHAMPCTAVval tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id =0;



 commit;





